package cn.buildoncloud.labor.workermanapplet.business.modules.services.impl;

import cn.buildoncloud.labor.workermanapplet.business.modules.services.WorkerManService;
import cn.buildoncloud.labor.workermanapplet.common.base.service.BaseServiceImpl;
import cn.buildoncloud.labor.workermanapplet.common.page.Pagination;
import org.nutz.dao.Dao;
import org.nutz.dao.Sqls;
import org.nutz.dao.entity.Record;
import org.nutz.dao.sql.Sql;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class WorkerManServiceImpl extends BaseServiceImpl<Map> implements WorkerManService {



    public WorkerManServiceImpl(Dao dao) {
        super(dao);
    }

    /**
     * 工友管理接口
     * @param search 搜索条件
     * @param pageNumber
     * @param pageSize
     * @param pId
     * @return
     */
    @Override
    public Pagination listPageWorkerMan(String search, int pageNumber, int pageSize, Long pId) {
        StringBuilder sb = new StringBuilder(" SELECT * from (SELECT CONCAT(b_w.id,'') id, CONCAT(b_r_w_tp.project_id,'') project_id, b_w.name workman_name, b_w.tel, b_w.sex, b_w.birth_date, TIMESTAMPDIFF(YEAR, b_w.birth_date, CURDATE()) age , b_w.id_card_no, CONCAT(b_w.head_photo_id,'') head_photo_id, b_wt.`name` workerteam_name, ");
        sb.append(" (SELECT name FROM sys_work_class where id = b_r_w_tp.work_class_id) work_class_name, ")
                .append(" (SELECT name FROM sys_work_type WHERE id = b_r_w_tp.work_type_id) work_type_name,  ")
                .append("  b_r_w_tp.workman_out_date ")
                .append(" FROM bi_workerman b_w ")
                .append(" LEFT JOIN bi_re_workman_and_teamproject b_r_w_tp on b_w.id = b_r_w_tp.worker_man_id ")
                .append(" LEFT JOIN bi_workerteam b_wt on b_w.worker_team_id = b_wt.id) t1 ")
                .append(" where project_id = @projectId and workman_out_date is null ");

        if (StringUtils.hasText(search)){
            sb.append(" and (workman_name like @search ")
                    .append(" or id_card_no like @search ")
                    .append(" or work_class_name like @search ")
                    .append(" or work_type_name like @search) ");
        }
        Sql sql = Sqls.create(sb.toString());
        sql.setParam("projectId", pId);
        if (StringUtils.hasText(search)){
            sql.setParam("search", "%" + search + "%");
        }
        return this.listPage(pageNumber, pageSize, sql);
    }



    @Override
    public Map<String, Object> getWorkerMan(Long wId) {
        StringBuilder sb = new StringBuilder("SELECT CONCAT(b_w.id,'') id, b_w.NAME workerman_name, b_w.id_card_no, CONCAT(b_w_e.frontpic_id,'') frontpic_id, CONCAT(b_w.head_photo_id,'') head_photo_id , CONCAT(b_w.id_card_photo_id,'') id_card_photo_id, b_w.tel,  b_w.birth_date, TIMESTAMPDIFF(YEAR, b_w.birth_date, CURDATE()) age , b_w.sex, b_w.nation, b_wt.`name` workerteam_name, b_l_s_c.`name` labor_name, ");
        sb.append(" (SELECT name FROM sys_work_class where id = b_r_w_tp.work_class_id) work_class_name, ")
                .append(" (SELECT name FROM sys_work_type WHERE id = b_r_w_tp.work_type_id) work_type_name, ")
                .append(" b_w.emergency_contact_tel, b_w.address, b_w.bloody, b_r_w_tp.calculation_of_salary, b_w.bank_account, b_w.open_account_bank ")
                .append(" FROM `bi_workerman` b_w ")
                .append(" LEFT JOIN bi_workerteam b_wt on b_w.worker_team_id = b_wt.id ")
                .append(" LEFT JOIN bi_labor_service_company b_l_s_c on b_wt.labor_service_company_id = b_l_s_c.id ")
                .append(" LEFT JOIN bi_re_workman_and_teamproject b_r_w_tp on b_w.id = b_r_w_tp.worker_man_id ")
                .append(" LEFT JOIN bi_workerman_enter b_w_e ON b_w.id_card_no = b_w_e.ID ")
                .append(" WHERE b_w.id = @wId ");
        Sql sql = Sqls.create(sb.toString());
        sql.setParam("wId", wId);
        List<Record> result = this.list(sql);
        if (result.size() > 0){
            return result.get(0);
        }
        return null;
    }

    /**
     *@create 2019/1/14 16:20
     *@Param [wId, pageNo, pageSize]
     *@return java.lang.Object
     *@desc  查询工人  (新增了曾参与项目 分页+排序)
     *
     * 修改-工友历史参建项目列表查询
     */
    @Override
    public List getWorkerManProject(Long wId, Integer pageNumber, Integer pageSize) {
        StringBuilder sb = new StringBuilder(" SELECT CONCAT(b_r_w_p.id,'') id,CONCAT(b_r_w_p.worker_man_id,'') worker_man_id,CONCAT(b_r_w_p.workman_in_date,' 至 ',IFNULL(b_r_w_p.workman_out_date,'现在')) workingTime,b_p.`name`,b_r_w_p.score,b_r_w_p.evaluate,b_r_w_p.bad_record_note,b_r_w_p.have_bad_record FROM bi_re_workman_and_teamproject b_r_w_p LEFT JOIN bi_project b_p ON b_r_w_p.project_id=b_p.id WHERE b_r_w_p.worker_man_id=@workerManId ORDER BY id DESC ");
        Sql sql = Sqls.create(sb.toString());
        sql.setParam("workerManId", wId);
//        List recordList = this.list(sql);
        Pagination pagination = this.listPage(pageNumber, pageSize, sql);
        if (pagination.getList() == null){
            return null;
        }
        return pagination.getList();
    }

    @Override
    public Integer workerManCount(String idCardNo) {
        StringBuilder sb = new StringBuilder("SELECT id FROM bi_workerman where id_card_no = @idCardNo");
        Sql sql = Sqls.create(sb.toString());
        sql.setParam("idCardNo", idCardNo);

        return this.count(sql);
    }

    @Override
    public Map<String, Object> getWorkerManByIdCardNo(String idCardNo) {
        StringBuilder sb = new StringBuilder(" SELECT distinct CONCAT(b_w.id, '') id, b_w.sex ,b_w.nation, b_w.tel, s_w_class.`name` work_class_name, s_w_type.`name` work_type_name, CONCAT(s_w_class.id, '') work_class_id, CONCAT(s_w_type.id, '') work_type_id ");
        sb.append(" FROM bi_workerman b_w ");
        sb.append(" LEFT JOIN bi_re_workman_and_teamproject b_r_w_t on b_w.id = b_r_w_t.worker_man_id ");
        sb.append(" LEFT JOIN sys_work_type s_w_type on b_r_w_t.work_type_id = s_w_type.id ");
        sb.append(" LEFT JOIN sys_work_class s_w_class on b_r_w_t.work_class_id = s_w_class.id ");
        sb.append(" where id_card_no = @idCardNo ");
        sb.append(" ORDER BY b_r_w_t.opAt DESC ");
        sb.append(" LIMIT 0,1 ");
        Sql sql = Sqls.create(sb.toString());
        sql.setParam("idCardNo", idCardNo);
        List<Record> result = this.list(sql);

        if (result.size() > 0){
            if (!StringUtils.hasText(result.get(0).get("work_class_id").toString())){
                return null;
            }
            if (!StringUtils.hasText(result.get(0).get("work_type_id").toString())){
                return null;
            }
            return result.get(0);
        }
        return null;
    }

    @Override
    public Map<String, Object> getWorkerManIdByIdCardNo(String idCardNo) {
        StringBuilder sb = new StringBuilder("SELECT CONCAT(id, '') id FROM bi_workerman where id_card_no = @idCardNo");
        Sql sql = Sqls.create(sb.toString());
        sql.setParam("idCardNo", idCardNo);
        List<Record> result = this.list(sql);
        if (result.size() > 0){
            return result.get(0);
        }
        return null;
    }

    @Override
    public Map<String, Object> getWorkerManHeadPhotoById(Long id) {
        StringBuilder sb = new StringBuilder(" SELECT CONCAT(head_photo_id, '') head_photo_id FROM bi_workerman where id = @id ");
        Sql sql = Sqls.create(sb.toString());
        sql.setParam("id", id);
        List<Record> result = this.list(sql);
        if (result.size() > 0){
            return result.get(0);
        }
        return new HashMap<>();
    }

    @Override
    public Map<String, Object> workerManIsEnter(String idCardNo, Long projectId) {
        StringBuilder sb = new StringBuilder(" SELECT CONCAT(w_m.id, '') id, CONCAT(w_t_p.project_id, '') project_id, w_t_p.wages_pattern FROM bi_workerman w_m ");
        sb.append(" LEFT JOIN bi_re_workman_and_teamproject w_t_p on w_m.id = w_t_p.worker_man_id ");
        sb.append(" where w_m.id_card_no = @idCardNo AND project_id = @projectId and w_t_p.wages_pattern = 1 ");

        Sql sql = Sqls.create(sb.toString());
        sql.setParam("idCardNo", idCardNo);
        sql.setParam("projectId", projectId);
        List<Record> result = this.list(sql);
        if (result.size() > 0){
            return result.get(0);
        }
        return null;
    }

    @Override
    public void updateWorkerManIdCard(String idCardNo, String idCardPhotoFront, String idCardPhotoReverse) {
        StringBuilder sb = new StringBuilder(" UPDATE bi_workerman SET ");
        sb.append(" id_card_photo_front = @idCardPhotoFront, ");
        sb.append(" id_card_photo_reverse = @idCardPhotoReverse ");
        sb.append(" WHERE id_card_no = @idCardNo ");

        Sql sql = Sqls.create(sb.toString());
        sql.setParam("idCardPhotoFront", idCardPhotoFront);
        sql.setParam("idCardPhotoReverse", idCardPhotoReverse);
        sql.setParam("idCardNo", idCardNo);

        this.execute(sql);
    }

    @Override
    public Map<String, Object> getWorkerManTeamproject(Long wId, Long pId) {
        StringBuilder sb = new StringBuilder("SELECT CONCAT(id, '') id from bi_re_workman_and_teamproject where worker_man_id = @wId and wages_pattern = 1 and project_id = @pId");
        Sql sql = Sqls.create(sb.toString());
        sql.setParam("wId", wId);
        sql.setParam("pId", pId);
        List<Record> result = this.list(sql);
        if (result.size() > 0){
            return result.get(0);
        }
        return null;
    }


}
